/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.vasthu.core.dao;

import com.vasthu.api.dao.BillDAO;
import com.vasthu.api.domain.Bill;
import com.vasthu.utility.VstDBManager;
import java.sql.Connection;
import java.util.List;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import org.apache.log4j.Logger;

/**
 *
 * @author moremada
 */
public class BillDAOImpl implements BillDAO {

    static Logger logger = Logger.getLogger(BillDAOImpl.class);

    @Override
    public boolean insertBill(Bill bill) {
        if (bill == null) {
            return false;
        }
        Connection con = VstDBManager.get().getConnection();
        PreparedStatement pst = null;

        try {
            pst = con.prepareStatement("INSERT INTO Bill(date, total_amount, user_id, status) "
                    + "VALUES(?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

            pst.setDate(1, new java.sql.Date(bill.getDate().getTime()));
            pst.setFloat(2, bill.getTotalAmount());
            pst.setLong(3, bill.getUserId());
            pst.setInt(4, bill.getStatus());
            pst.executeUpdate();
            ResultSet rst = pst.getGeneratedKeys();
            if (rst.next()) {
                long billId = rst.getInt(1);
                bill.setBillId(billId);
            }

        } catch (Exception ex) {
            logger.error("unable to insert Bill : " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return true;
    }

    @Override
    public Bill getBillById(long billId, int status) {
        PreparedStatement pst = null;
        Bill bill = new Bill();
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Bill WHERE bill_id = ? AND status = ?");
            pst.setLong(1, billId);
            pst.setInt(2, status);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                bill.setBillId(rs.getLong("bill_id"));
                bill.setDate(rs.getDate("date"));
                bill.setTotalAmount(rs.getFloat("total_amount"));
                bill.setUserId(rs.getLong("user_Id"));
                bill.setStatus(rs.getInt("status"));
            }
        } catch (Exception ex) {
            logger.error("unable to retrive bill bill id =  " + billId + " " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return bill;
    }

    @Override
    public boolean deleteBillById(long billId) {
        boolean state = false;
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("DLETE FROM Bill WHERE (bill_id=?)");
            pst.setLong(0, billId);
            pst.executeUpdate();
            state = true;

        } catch (Exception ex) {
            logger.error("unable to delete bill bill id =  " + billId + " " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return state;
    }

    @Override
    public List<Bill> getAllBills() {
        List<Bill> allBills = new ArrayList<Bill>();
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Bill");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                Bill bill = new Bill();
                bill.setBillId(rs.getLong(0));
                bill.setDate(rs.getDate(1));
                bill.setTotalAmount(rs.getFloat(2));
                bill.setUserId(rs.getLong(3));
                allBills.add(bill);
            }
        } catch (Exception ex) {
            logger.error("Unable to retrive bills" + ex);
        } finally {
            VstDBManager.get().close();
        }
        return allBills;
    }

    @Override
    public List<Bill> getBillByUserIdAndState(long userId, int status) {
        List<Bill> bills = new ArrayList<Bill>();
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Bill WHERE user_id = ? AND status = ?");
            pst.setLong(1, userId);
            pst.setInt(2, status);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                Bill bill = new Bill();
                bill.setBillId(rs.getLong("bill_id"));
                bill.setDate(rs.getDate("date"));
                bill.setTotalAmount(rs.getFloat("total_amount"));
                bill.setUserId(rs.getLong("user_Id"));
                bill.setStatus(rs.getInt("status"));
                bills.add(bill);
            }
        } catch (Exception ex) {
            logger.error("Unable to retrive bills for user_id =  " + userId + " and state = " + status + ex);
        } finally {
            VstDBManager.get().close();
        }
        return bills;
    }

    @Override
    public boolean updateTotal(Bill bill) {
        if (bill == null) {
            return false;
        }
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("UPDATE bill SET total_amount = ? WHERE bill_id = ?");

            pst.setFloat(1, bill.getTotalAmount());
            pst.setLong(2, bill.getBillId());
            pst.executeUpdate();

        } catch (Exception ex) {
            logger.error("unable to update total of the bill : " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return true;
    }
}
